﻿using EMoney.IStock.Client.Models.Tclz;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EMoney.IStock.Client.DataAccess.Tclz
{
    /// <summary>
    /// 题材领涨数据访问类
    /// </summary>
    public class TclzDal
    {
        /// <summary>
        /// 获取指定区间 题材优选 个股信息
        /// </summary>
        /// <param name="dtBegin"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public List<VM_CHASE_STK_LIST> GetList(DateTime dtBegin, DateTime dtEnd)
        {
            string sql = "SELECT * FROM VM_CHASE_STK_LIST WHERE [DATE]>=@begin and [DATE]<@end order by DATE desc";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("begin", System.Data.SqlDbType.DateTime, dtBegin);
            tools.AddSqlParameter("end", System.Data.SqlDbType.DateTime, dtEnd);

            return SQLHelper.GetDataByColumnsLst<VM_CHASE_STK_LIST>(ConnectionManager.Fplat, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取指定区间、指定关键字 题材优选 详情信息(除当前股票以外)
        /// </summary>
        /// <param name="secucode"></param>
        /// <param name="standard"></param>
        /// <param name="dtBegin"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public List<VM_CHASE_STK_DETAIL> GetDetail(string secucode, string standard, DateTime dtBegin, DateTime dtEnd, string time)
        {
            string sql = "SELECT Top 5 * FROM VM_CHASE_STK_DETAIL WHERE [DATE]<@date and secucode=@secucode and standard=@standard order by DATE desc"; // and [DATE]>=@begin and [DATE]<@end

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("date", System.Data.SqlDbType.DateTime, time);
            tools.AddSqlParameter("secucode", System.Data.SqlDbType.VarChar, 50, secucode);
            tools.AddSqlParameter("standard", System.Data.SqlDbType.VarChar, 100, standard);
            //tools.AddSqlParameter("begin", System.Data.SqlDbType.DateTime, dtBegin);
            //tools.AddSqlParameter("end", System.Data.SqlDbType.DateTime, dtEnd);

            return SQLHelper.GetDataByColumnsLst<VM_CHASE_STK_DETAIL>(ConnectionManager.Fplat, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取成功率信息
        /// </summary>
        /// <returns></returns>
        public List<CHASE_SUCCESSRATE> GetSuccessRate()
        {
            string sql = "SELECT * FROM CHASE_SUCCESSRATE ORDER BY the_year,quarter";

            return SQLHelper.GetDataByColumnsLst<CHASE_SUCCESSRATE>(ConnectionManager.Fplat, sql, null);
        }

        /// <summary>
        /// 获取指定区间内异动股票总个数
        /// </summary>
        /// <param name="dtBegin"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public int GetTotalNumber(DateTime dtBegin, DateTime dtEnd)
        {
            string sql = "select COUNT(DISTINCT f025v_12001) from vm_dms_getnewsbycode WITH(NOLOCK) where code ='3070505' and f035d_12001 >= @begin AND f035d_12001 < @end";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("begin", System.Data.SqlDbType.DateTime, dtBegin);
            tools.AddSqlParameter("end", System.Data.SqlDbType.DateTime, dtEnd);

            return Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionManager.Fplat, System.Data.CommandType.Text, sql, tools.GetSqlParameters()));
        }

        /// <summary>
        /// 获取今日异动股票数量
        /// </summary>
        /// <returns></returns>
        public int GetTodayNumber()
        {
            string sql = "select count(DISTINCT SecuCode) from VM_CHASE_STK_LIST where DATE>=@date";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("date", System.Data.SqlDbType.DateTime, DateTime.Now.Date);

            return Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionManager.Fplat, System.Data.CommandType.Text, sql, tools.GetSqlParameters()));
        }

        /// <summary>
        /// 获取一年半以来策略战绩
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public List<VM_CHASE_STK_LIST> GetRecord(DateTime date)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT TOP 30 * FROM(");
            sql.Append("SELECT secucode,secuabbr,date,standard,reason,closerise");
            sql.Append(",ROW_NUMBER() OVER(PARTITION BY SecuCode ORDER BY closerise DESC) AS gindex ");
            sql.Append("FROM VM_CHASE_STK_LIST WITH(NOLOCK) ");
            sql.Append("WHERE closerise>0.5 and DATE>=@date");
            sql.Append(") AS RS WHERE gindex=1 ORDER BY  closerise DESC");

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("date", System.Data.SqlDbType.DateTime, date);

            return SQLHelper.GetDataByColumnsLst<VM_CHASE_STK_LIST>(ConnectionManager.Fplat, sql.ToString(), tools.GetSqlParameters());
        }
    }
}
